<?php

class m130907_042530_init_db extends CDbMigration
{
	// public function up(){
	// 	$this->createTable('users', array(

	// 	));
	// }

	// public function down()
	// {
	// 	echo "m130907_042530_init_db does not support migration down.\n";
	// 	return false;
	// }

	
	// Use safeUp/safeDown to do migration with transaction
	public function safeUp(){
		$this->execute('
			-- -----------------------------------------------------
			-- Table `ham_user_group`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_user_group` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `name` VARCHAR(100) NULL,
			  `name_key` VARCHAR(100) NULL,
			  PRIMARY KEY (`id`),
			  UNIQUE INDEX `name_key_UNIQUE` (`name_key` ASC))
			ENGINE = InnoDB;


			-- -----------------------------------------------------
			-- Table `ham_user`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_user` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `username` VARCHAR(200) NOT NULL,
			  `email` VARCHAR(255) NOT NULL,
			  `password` VARCHAR(128) NOT NULL,
			  `status` TINYINT(1) NOT NULL DEFAULT TRUE,
			  `group_id` INT(2) NULL,
			  `created_at` TIMESTAMP NULL,
			  `updated_at` TIMESTAMP NULL,
			  PRIMARY KEY (`id`),
			  UNIQUE INDEX `username_UNIQUE` (`username` ASC),
			  UNIQUE INDEX `email_UNIQUE` (`email` ASC),
			  INDEX `fk_ham_user_ham_user_group1_idx` (`group_id` ASC),
			  CONSTRAINT `fk_ham_user_ham_user_group1`
			    FOREIGN KEY (`group_id`)
			    REFERENCES `ham_user_group` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION)
			ENGINE = InnoDB;


			-- -----------------------------------------------------
			-- Table `ham_user_profile`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_user_profile` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `ful_name` VARCHAR(200) NULL,
			  `address` VARCHAR(200) NULL,
			  `phone` VARCHAR(20) NULL,
			  `user_id` INT NOT NULL,
			  PRIMARY KEY (`id`, `user_id`),
			  INDEX `fk_ham_user_profile_ham_user1_idx` (`user_id` ASC),
			  CONSTRAINT `fk_ham_user_profile_ham_user1`
			    FOREIGN KEY (`user_id`)
			    REFERENCES `ham_user` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION)
			ENGINE = InnoDB
			DEFAULT CHARACTER SET = utf8
			COLLATE = utf8_unicode_ci;


			-- -----------------------------------------------------
			-- Table `ham_user_log`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_user_log` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `user_id` INT NOT NULL,
			  `date_time` TIMESTAMP NULL,
			  `ip` VARCHAR(45) NULL,
			  `device` VARCHAR(50) NULL,
			  `os` VARCHAR(50) NULL,
			  `browser` VARCHAR(50) NULL,
			  `browser_version` VARCHAR(45) NULL,
			  PRIMARY KEY (`id`),
			  CONSTRAINT `fk_ham_user_log_ham_user`
			    FOREIGN KEY (`id`)
			    REFERENCES `ham_user` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION)
			ENGINE = InnoDB
			DEFAULT CHARACTER SET = utf8
			COLLATE = utf8_unicode_ci;


			-- -----------------------------------------------------
			-- Table `ham_account`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_account` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `secret_id` VARCHAR(128) NOT NULL,
			  `account_remain` FLOAT NOT NULL,
			  `salt` VARCHAR(45) NOT NULL,
			  `user_id` INT NOT NULL,
			  PRIMARY KEY (`id`, `user_id`),
			  UNIQUE INDEX `secret_id_UNIQUE` (`secret_id` ASC),
			  INDEX `fk_ham_tai_khoan_ham_user1_idx` (`user_id` ASC),
			  CONSTRAINT `fk_ham_tai_khoan_ham_user1`
			    FOREIGN KEY (`user_id`)
			    REFERENCES `ham_user` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION)
			ENGINE = InnoDB
			DEFAULT CHARACTER SET = utf8
			COLLATE = utf8_unicode_ci;


			-- -----------------------------------------------------
			-- Table `ham_account_transaction`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_account_transaction` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `type` VARCHAR(50) NOT NULL,
			  `transaction_amount` FLOAT NOT NULL,
			  `account_id` INT NOT NULL,
			  `date_time` TIMESTAMP NOT NULL,
			  `tran_cert` VARCHAR(128) NOT NULL,
			  PRIMARY KEY (`id`),
			  INDEX `fk_ham_giao_dich_tai_khoan_ham_tai_khoan1_idx` (`account_id` ASC),
			  CONSTRAINT `fk_ham_giao_dich_tai_khoan_ham_tai_khoan1`
			    FOREIGN KEY (`account_id`)
			    REFERENCES `ham_account` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION)
			ENGINE = InnoDB
			DEFAULT CHARACTER SET = utf8
			COLLATE = utf8_unicode_ci;


			-- -----------------------------------------------------
			-- Table `ham_contact`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_contact` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `email` VARCHAR(255) NOT NULL,
			  `full_name` VARCHAR(200) NULL,
			  `phone` VARCHAR(20) NULL,
			  `address` VARCHAR(200) NULL,
			  `status` TINYINT(1) NOT NULL DEFAULT TRUE,
			  `unsubscribe` TINYINT(1) NOT NULL DEFAULT FALSE,
			  `created_at` TIMESTAMP NOT NULL,
			  `created_by` INT NOT NULL,
			  PRIMARY KEY (`id`),
			  INDEX `fk_ham_contact_ham_user1_idx` (`created_by` ASC),
			  CONSTRAINT `fk_ham_contact_ham_user1`
			    FOREIGN KEY (`created_by`)
			    REFERENCES `ham_user` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION)
			ENGINE = InnoDB
			DEFAULT CHARACTER SET = utf8
			COLLATE = utf8_unicode_ci;


			-- -----------------------------------------------------
			-- Table `ham_contact_custom_field`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_contact_custom_field` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `name` VARCHAR(100) NOT NULL,
			  `name_key` VARCHAR(100) NOT NULL,
			  `type` VARCHAR(50) NOT NULL,
			  `description` TEXT NULL,
			  `created_at` TIMESTAMP NOT NULL,
			  `created_by` INT NOT NULL,
			  PRIMARY KEY (`id`),
			  INDEX `fk_ham_contact_custom_field_ham_user1_idx` (`created_by` ASC),
			  CONSTRAINT `fk_ham_contact_custom_field_ham_user1`
			    FOREIGN KEY (`created_by`)
			    REFERENCES `ham_user` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION)
			ENGINE = InnoDB
			DEFAULT CHARACTER SET = utf8
			COLLATE = utf8_unicode_ci;


			-- -----------------------------------------------------
			-- Table `ham_contact_custom_value`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_contact_custom_value` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `custom_field_id` INT NOT NULL,
			  `value` TEXT NULL,
			  `contact_id` INT NOT NULL,
			  PRIMARY KEY (`id`),
			  INDEX `fk_ham_contact_custom_value_ham_contact1_idx` (`contact_id` ASC),
			  INDEX `fk_ham_contact_custom_value_ham_contact_custom_field1_idx` (`custom_field_id` ASC),
			  CONSTRAINT `fk_ham_contact_custom_value_ham_contact1`
			    FOREIGN KEY (`contact_id`)
			    REFERENCES `ham_contact` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION,
			  CONSTRAINT `fk_ham_contact_custom_value_ham_contact_custom_field1`
			    FOREIGN KEY (`custom_field_id`)
			    REFERENCES `ham_contact_custom_field` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION)
			ENGINE = InnoDB
			DEFAULT CHARACTER SET = utf8
			COLLATE = utf8_unicode_ci;


			-- -----------------------------------------------------
			-- Table `ham_template`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_template` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `name` VARCHAR(200) NOT NULL,
			  `description` VARCHAR(255) NULL,
			  `content` TEXT NULL,
			  `is_system` TINYINT(1) NOT NULL DEFAULT FALSE,
			  `created_at` TIMESTAMP NOT NULL,
			  `updated_at` TIMESTAMP NULL,
			  `created_by` INT NULL,
			  PRIMARY KEY (`id`),
			  INDEX `fk_ham_template_ham_user1_idx` (`created_by` ASC),
			  CONSTRAINT `fk_ham_template_ham_user1`
			    FOREIGN KEY (`created_by`)
			    REFERENCES `ham_user` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION)
			ENGINE = InnoDB
			DEFAULT CHARACTER SET = utf8
			COLLATE = utf8_unicode_ci;


			-- -----------------------------------------------------
			-- Table `ham_campaign`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_campaign` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `name` VARCHAR(255) NOT NULL,
			  `description` TEXT NULL,
			  `utm_campaign` VARCHAR(255) NULL,
			  `utm_medium` VARCHAR(255) NULL,
			  `utm_source` VARCHAR(255) NULL,
			  `utm_term` VARCHAR(255) NULL,
			  `utm_content` VARCHAR(255) NULL,
			  `created_at` TIMESTAMP NOT NULL,
			  `updated_at` TIMESTAMP NULL,
			  `created_by` INT NOT NULL,
			  `template_id` INT NULL,
		  PRIMARY KEY (`id`),
		  INDEX `fk_ham_campain_ham_user1_idx` (`created_by` ASC),
		  INDEX `fk_ham_campaign_ham_template1_idx` (`template_id` ASC),
		  CONSTRAINT `fk_ham_campain_ham_user1`
		    FOREIGN KEY (`created_by`)
		    REFERENCES `ham`.`ham_user` (`id`)
		    ON DELETE NO ACTION
		    ON UPDATE NO ACTION,
		  CONSTRAINT `fk_ham_campaign_ham_template1`
		    FOREIGN KEY (`template_id`)
		    REFERENCES `ham`.`ham_template` (`id`)
		    ON DELETE NO ACTION
		    ON UPDATE NO ACTION)
			ENGINE = InnoDB
			DEFAULT CHARACTER SET = utf8
			COLLATE = utf8_unicode_ci;



			-- -----------------------------------------------------
			-- Table `ham_campaign_schedule`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_campaign_schedule` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `campaign_id` INT NOT NULL,
			  PRIMARY KEY (`id`, `campaign_id`),
			  INDEX `fk_ham_campain_schedule_ham_campain1_idx` (`campaign_id` ASC),
			  CONSTRAINT `fk_ham_campain_schedule_ham_campain1`
			    FOREIGN KEY (`campaign_id`)
			    REFERENCES `ham_campaign` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION)
			ENGINE = InnoDB
			DEFAULT CHARACTER SET = utf8
			COLLATE = utf8_unicode_ci;


			-- -----------------------------------------------------
			-- Table `ham_campaign_statistic`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_campaign_statistic` (
		  `id` INT NOT NULL AUTO_INCREMENT,
		  `send` INT NULL,
		  `open` INT NULL,
		  `unsubscribe` INT NULL,
		  `campaign_id` INT NOT NULL,
		  PRIMARY KEY (`id`),
		  INDEX `fk_ham_campaign_statistic_ham_campaign1_idx` (`campaign_id` ASC),
		  CONSTRAINT `fk_ham_campaign_statistic_ham_campaign1`
		    FOREIGN KEY (`campaign_id`)
		    REFERENCES `ham`.`ham_campaign` (`id`)
		    ON DELETE NO ACTION
		    ON UPDATE NO ACTION)
			ENGINE = InnoDB
			DEFAULT CHARACTER SET = utf8
			COLLATE = utf8_unicode_ci;


			-- -----------------------------------------------------
			-- Table `ham_contact_campaign`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_contact_campaign` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `contact_id` INT NULL,
			  `email` VARCHAR(255) NOT NULL,
			  `campaign_id` INT NOT NULL,
			  PRIMARY KEY (`id`),
			  INDEX `fk_ham_contact_campaign_ham_campain1_idx` (`campaign_id` ASC),
			  INDEX `fk_ham_contact_campaign_ham_contact1_idx` (`contact_id` ASC),
			  CONSTRAINT `fk_ham_contact_campaign_ham_campain1`
			    FOREIGN KEY (`campaign_id`)
			    REFERENCES `ham_campaign` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION,
			  CONSTRAINT `fk_ham_contact_campaign_ham_contact1`
			    FOREIGN KEY (`contact_id`)
			    REFERENCES `ham_contact` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION)
			ENGINE = InnoDB
			DEFAULT CHARACTER SET = utf8
			COLLATE = utf8_unicode_ci;


			-- -----------------------------------------------------
			-- Table `ham_contact_statistic`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_contact_statistic` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `contact_campaign_id` INT NOT NULL,
			  `contact_statistic_id` INT NOT NULL,
			  `send` TINYINT(1) NOT NULL DEFAULT FALSE,
			  `open` TINYINT(1) NOT NULL DEFAULT FALSE,
			  `unsubscribe` TINYINT(1) NOT NULL DEFAULT FALSE,
			  `open_datetime` TIMESTAMP NULL,
			  PRIMARY KEY (`id`),
			  INDEX `fk_ham_contact_statistic_ham_contact_campaign1_idx` (`contact_campaign_id` ASC),
			  INDEX `fk_ham_contact_statistic_ham_campaign_statistic1_idx` (`contact_statistic_id` ASC),
			  CONSTRAINT `fk_ham_contact_statistic_ham_contact_campaign1`
			    FOREIGN KEY (`contact_campaign_id`)
			    REFERENCES `ham_contact_campaign` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION,
			  CONSTRAINT `fk_ham_contact_statistic_ham_campaign_statistic1`
			    FOREIGN KEY (`contact_statistic_id`)
			    REFERENCES `ham_campaign_statistic` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION)
			ENGINE = InnoDB;


			-- -----------------------------------------------------
			-- Table `ham_contact_campaign_track`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_contact_campaign_track` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `track_type` VARCHAR(200) NOT NULL,
			  `track_value` VARCHAR(255) NULL,
			  `datetime` TIMESTAMP NULL,
			  `contact_statistic_id` INT NULL,
			  PRIMARY KEY (`id`),
			  INDEX `fk_ham_contact_campaign_track_ham_contact_statistic1_idx` (`contact_statistic_id` ASC),
			  CONSTRAINT `fk_ham_contact_campaign_track_ham_contact_statistic1`
			    FOREIGN KEY (`contact_statistic_id`)
			    REFERENCES `ham_contact_statistic` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION)
			ENGINE = InnoDB
			DEFAULT CHARACTER SET = utf8
			COLLATE = utf8_unicode_ci;



			-- -----------------------------------------------------
			-- Table `ham_account_information`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_account_information` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `account_type` VARCHAR(50) NOT NULL,
			  `account_remain` FLOAT NOT NULL,
			  `user_id` INT NOT NULL,
			  PRIMARY KEY (`id`, `user_id`),
			  INDEX `fk_ham_account_information_ham_user1_idx` (`user_id` ASC),
			  CONSTRAINT `fk_ham_account_information_ham_user1`
			    FOREIGN KEY (`user_id`)
			    REFERENCES `ham_user` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION)
			ENGINE = InnoDB
			DEFAULT CHARACTER SET = utf8
			COLLATE = utf8_unicode_ci;



			-- -----------------------------------------------------
			-- Table `ham_group`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_group` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `name` VARCHAR(255) NOT NULL,
			  `description` VARCHAR(255) NULL,
			  `created_at` TIMESTAMP NOT NULL,
			  `created_by` INT NOT NULL,
			  PRIMARY KEY (`id`),
			  INDEX `fk_ham_circle_ham_user1_idx` (`created_by` ASC),
			  CONSTRAINT `fk_ham_circle_ham_user1`
			    FOREIGN KEY (`created_by`)
			    REFERENCES `ham_user` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION)
			ENGINE = InnoDB;


			-- -----------------------------------------------------
			-- Table `ham_group_contact`
			-- -----------------------------------------------------
			CREATE TABLE IF NOT EXISTS `ham_group_contact` (
			  `id` INT NOT NULL AUTO_INCREMENT,
			  `group_id` INT NOT NULL,
			  `contact_id` INT NOT NULL,
			  `status` TINYINT(1) NOT NULL DEFAULT TRUE,
			  `unsubscribe` TINYINT(1) NULL DEFAULT FALSE,
			  PRIMARY KEY (`id`),
			  INDEX `fk_ham_circle_contact_ham_circle1_idx` (`group_id` ASC),
			  INDEX `fk_ham_circle_contact_ham_contact1_idx` (`contact_id` ASC),
			  CONSTRAINT `fk_ham_circle_contact_ham_circle1`
			    FOREIGN KEY (`group_id`)
			    REFERENCES `ham_group` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION,
			  CONSTRAINT `fk_ham_circle_contact_ham_contact1`
			    FOREIGN KEY (`contact_id`)
			    REFERENCES `ham_contact` (`id`)
			    ON DELETE NO ACTION
			    ON UPDATE NO ACTION)
			ENGINE = InnoDB
			DEFAULT CHARACTER SET = utf8
			COLLATE = utf8_unicode_ci;
		');
		// init users
		$this->insert('ham_user_group', array(
			'id' 	=> 1,
			'name'	=> 'Administrator',
			'name_key'=> 'admin'
		));
		$this->insert('ham_user_group', array(
			'id' 	=> 2,
			'name'	=> 'Staff',
			'name_key'=> 'staff'
		));
		$this->insert('ham_user_group', array(
			'id' 	=> 3,
			'name'	=> 'Customer',
			'name_key'=> 'customer'
		));
		$this->insert('ham_user', array(
			'id' => 1,
			'username' => 'admin',
			'email'    => 'admin@gmail.com',
			'password' => '$2a$13$YYNhOrPueLBk3XFaLGiZIuOLWmyRBU2Vs9l9CWDv0nkwZnEyX0QkC',
			'status'   => 1,
			'group_id' => 1
		));
		$this->insert('ham_user', array(
			'id' => 2,
			'username' => 'demo',
			'email'    => 'demo@gmail.com',
			'password' => '$2a$13$YYNhOrPueLBk3XFaLGiZIuOLWmyRBU2Vs9l9CWDv0nkwZnEyX0QkC',
			'status'   => 1,
			'group_id' => 3
		));
	}

	public function safeDown(){
	}
	
}